﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;
using System.Windows.Forms;
namespace BookStore.Controllers
{
    public class ProductController : Controller
    {
        private static ProductController _controller = null;
        public static ProductController getController()
        {
            if (_controller == null)
                return new ProductController();
            else
                return _controller;
        }
        public ProductController()
        {
            _controller = this;
        }
        public DataTable getAllProduct()
        {            
            DataTable all = new DataTable("Product");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetAllProduct", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataReader sda = cmd.ExecuteReader();
                all.Load(sda);
            }
            catch (Exception e)
            {
               // throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return all;
        }


        public DataTable searchProductByID(string AuthorID, string CategoryID, string LocationID, string ManufacturerID, string ProductID)
        {
            con = BSCommon.getConnection();
            DataTable pid = new DataTable("Product");
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_SearchProductByID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID + "%";
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID + "%";
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID + "%";
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID + "%";
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = ProductID + "%";
                SqlDataReader sdr = cmd.ExecuteReader();                
                pid.Load(sdr);
            }
            catch (Exception e)
            {
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return pid;
        }

        public DataTable searchProductByName(string AuthorName, string CategoryName, string LocationName, string ManufacturerName, string ProductName)
        {
            con = BSCommon.getConnection();
            DataTable pid = new DataTable("Product");
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_SearchProductByName", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AuthorName", SqlDbType.NVarChar).Value = AuthorName + "%";
                cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = CategoryName + "%";
                cmd.Parameters.Add("@LocationName", SqlDbType.NVarChar).Value = LocationName + "%";
                cmd.Parameters.Add("@ManufacturerName", SqlDbType.NVarChar).Value = ManufacturerName + "%";
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName + "%";
                SqlDataReader sdr = cmd.ExecuteReader();                
                pid.Load(sdr);
            }
            catch (Exception e)
            {
                //throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return pid;
        }

        public DataTable SearchProduct(String ProductID, String ProductName, String LocationID, String CategoryID, String ManufacturerID, String AuthorID, String SellingPrice, String StoringPrice)
        {
            con = BSCommon.getConnection();
            DataTable pid = new DataTable("SearchProduct");
            try
            {
                con.Open();
                if ((SellingPrice != "") && (StoringPrice != ""))
                {
                    SqlCommand cmd = new SqlCommand("BS_SearchProduct", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName + "%";
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID + "%";
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID + "%";
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID + "%";
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID + "%";
                    cmd.Parameters.Add("@SellingPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(SellingPrice);
                    cmd.Parameters.Add("@StoringPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(StoringPrice);
                    SqlDataReader sdr = cmd.ExecuteReader();
                    pid.Load(sdr);
                }
                else if (SellingPrice != "")
                {
                    SqlCommand cmd = new SqlCommand("BS_SearchProductFromSellingPrice", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName + "%";
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID + "%";
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID + "%";
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID + "%";
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID + "%";
                    cmd.Parameters.Add("@SellingPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(SellingPrice);
                    SqlDataReader sdr = cmd.ExecuteReader();
                    pid.Load(sdr);
                }
                else
                {
                    SqlCommand cmd = new SqlCommand("BS_SearchProductFromStoringPrice", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName + "%";
                    cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID + "%";
                    cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID + "%";
                    cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID + "%";
                    cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID + "%";
                    cmd.Parameters.Add("@StoringPrice", SqlDbType.Decimal).Value = Convert.ToDecimal(StoringPrice);
                    SqlDataReader sdr = cmd.ExecuteReader();
                    pid.Load(sdr);
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return pid;
        }

        public DataTable SearchProduct(String ProductID, String ProductName, String LocationID, String CategoryID, String ManufacturerID, String AuthorID)
        {
            con = BSCommon.getConnection();
            DataTable pid = new DataTable("SearchProduct");
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_SearchProductNonPrice", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName + "%";
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID + "%";
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID + "%";
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID + "%";
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID + "%";
                SqlDataReader sdr = cmd.ExecuteReader();
                pid.Load(sdr);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return pid;
        }

        public Product getProductByID(string productName)
        {
            Product product = new Product();
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("Select * from Product where ProductID ='" + productName + "';", con);
                cmd.CommandType = CommandType.Text;
                SqlDataReader sdr = cmd.ExecuteReader();
                DataTable dt = new DataTable("Product");
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                {
                    DataRow dr = dt.Rows[0];
                    product = new Product();
                    product.ProductID = Convert.ToString(dr["ProductID"]);
                    product.ProductName = Convert.ToString(dr["ProductName"]);
                    product.SellingPrice = Convert.ToDecimal(dr["SellingPrice"]);
                    product.StoringPrice = Convert.ToDecimal(dr["StoringPrice"]);
                    product.Fk_AuthorID = Convert.ToString(dr["AuthorID"]); ;
                    product.Fk_CategoryID = Convert.ToString(dr["CategoryID"]);
                    product.Fk_LocationID = Convert.ToString(dr["LocationID"]);
                    product.Fk_ManufacturerID = Convert.ToString(dr["ManufacturerID"]);
                }
                else
                    return null;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }            
            return product;
        }

        public String getProductID(string productName)
        {
            DataTable dt = new DataTable("getProductID");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand("BS_GetProductID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = productName;
                var result = cmd.ExecuteReader();
                dt.Load(result);
                return dt.Rows[0][0].ToString();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public String getNumericCodeOfProduct()
        {
            String sql = @"SELECT [ProductID] FROM [BookStore].[dbo].[PRODUCT]";
            String productID;
            DataTable dt = new DataTable("getNumericCodeOfProduct");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                SqlDataReader result = cmd.ExecuteReader();
                dt.Load(result);
                int i = dt.Rows.Count;
                productID = (i + 1).ToString();
                while (productID.Length < 5)
                {
                    productID = "0" + productID;
                }
                return productID;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void AddProductToDatabase(String ProductID, String ProductName, String LocationID, String CategoryID, String ManufacturerID, String AuthorID, String SellingPrice, String StoringPrice)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_AddProductToDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = ProductID;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName;
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID;
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID;
                cmd.Parameters.Add("@SellingPrice", SqlDbType.Decimal).Value = SellingPrice;
                cmd.Parameters.Add("@StoringPrice", SqlDbType.Decimal).Value = StoringPrice;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void DeleteProductFromDatabase(String productID)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_DeleteProductFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = productID;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Deleted successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to delete this product because it's attribute!", "Error : Delete Product", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void ModifyProductFromDatabase(String ProductID, String ProductName, String LocationID, String CategoryID, String ManufacturerID, String AuthorID, String SellingPrice, String StoringPrice)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_ModifyProductFromDatabase", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = ProductID;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName;
                cmd.Parameters.Add("@LocationID", SqlDbType.NVarChar).Value = LocationID;
                cmd.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = CategoryID;
                cmd.Parameters.Add("@ManufacturerID", SqlDbType.NVarChar).Value = ManufacturerID;
                cmd.Parameters.Add("@AuthorID", SqlDbType.NVarChar).Value = AuthorID;
                cmd.Parameters.Add("@SellingPrice", SqlDbType.Decimal).Value = SellingPrice;
                cmd.Parameters.Add("@StoringPrice", SqlDbType.Decimal).Value = StoringPrice;
                cmd.ExecuteNonQuery();
                tran.Commit();
                MessageBox.Show("Modified successful!", "Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception e)
            {
                tran.Rollback();
                //throw e;
                MessageBox.Show("You don't permission to modify this product because it's attribute!", "Error : Modify Product", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public DataTable DisplayProduct(String product, int check)
        {
            DataTable dt = new DataTable("DisplayProduct");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Display Product From ProductID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_DisplayProductFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = product;
                }
                // Display Product From ProductName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_DisplayProductFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = product;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public DataTable DisplayAllProduct()
        {
            DataTable dt = new DataTable("DisplayAllProduct");
            String sql = @"SELECT [ProductID],[ProductName],[LocationID],[CategoryID],[ManufacturerID],[AuthorID],[SellingPrice],[StoringPrice]
                           FROM [BookStore].[dbo].[PRODUCT]";
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        public Boolean CheckProductInDatabase(String product, int check)
        {
            DataTable dt = new DataTable("CheckProductInDatabase");
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                // Check Product From ProductID, check = 0
                if (check == 0)
                {
                    cmd = new SqlCommand("BS_CheckProductFromID", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = product;
                }
                // Check Product From ProductName, check = 1
                else
                {
                    cmd = new SqlCommand("BS_CheckProductFromName", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = product;
                }
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                if (dt.Rows.Count > 0)
                    return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return false;
        }

        public Boolean CheckProductName(String productName)
        {
            for (int i = 0; i < productName.Length; i++)
            {
                if ((productName[i].ToString() == "\\") || (productName[i].ToString() == ";") || (productName[i].ToString() == ")") || (productName[i].ToString() == "@") ||
                    (productName[i].ToString() == "^") || (productName[i].ToString() == "$") || (productName[i].ToString() == "%") || (productName[i].ToString() == "&"))
                    return false;
            }
            return true;
        }
    }
}
